package com.dorm.mapper;

import com.dorm.entity.HygieneRecord;
import org.apache.ibatis.annotations.*;
import java.util.List;

@Mapper
public interface HygieneMapper {
    @Select("SELECT * FROM hygiene_records WHERE room_id = #{roomId} ORDER BY check_time DESC")
    @Results({
        @Result(property = "id", column = "id"),
        @Result(property = "roomId", column = "room_id"),
        @Result(property = "score", column = "score"),
        @Result(property = "comments", column = "comments"),
        @Result(property = "checkTime", column = "check_time")
    })
    List<HygieneRecord> findByRoomId(Long roomId);

    @Insert("INSERT INTO hygiene_records(room_id, score, comments, check_time) " +
            "VALUES(#{roomId}, #{score}, #{comments}, #{checkTime})")
    @Options(useGeneratedKeys = true, keyProperty = "id")
    int insert(HygieneRecord record);

    @Delete("DELETE FROM hygiene_records WHERE id = #{id}")
    int deleteById(Long id);

    @Select("SELECT AVG(score) FROM hygiene_records WHERE room_id IN " +
            "(SELECT id FROM rooms WHERE building_id = #{buildingId})")
    Double getAverageScoreByBuilding(Long buildingId);

    @Select("SELECT COUNT(*) FROM hygiene_records WHERE room_id IN " +
            "(SELECT id FROM rooms WHERE building_id = #{buildingId})")
    Integer getCheckedRoomCountByBuilding(Long buildingId);

    @Select("SELECT MAX(check_time) FROM hygiene_records WHERE room_id IN " +
            "(SELECT id FROM rooms WHERE building_id = #{buildingId})")
    String getLastCheckTimeByBuilding(Long buildingId);
} 